livecodefandomcom-20200223-history
RevExecuteSQL
Executes a SQL statement on a database. Syntax: revExecuteSQL , | } Examples: revExecuteSQL myID, "insert into mytable values('Red', 'Blue')" revExecuteSQL myID, "insert into mytable values(:1,:2)", "valueX" ,"valueY" revExecuteSQL myID, "insert into mytable values(:1,:2)", "*bvalueX" ,"*bvalueY" revExecuteSQL myDatabaseID, the text of field "Query", "*bMyVar" revExecuteSQL 12, builtQuery, "someArray" Use the revExecuteSQL command to execute a SQL query without quering records. You can work at the same time, with the same connection ID, with all databases that your user and password allow you. So this is a working code: revExecuteSQL 12,"INSERT INTO DatabaseName2.TableName2 SELECT * from DatabaseName1.TableName1" If you want to use transactions, on MySQL you need to use them on a single command, this way: put "UPDATE products SET qtyinstock=0 WHERE productname='green' ;" after tSQL put "UPDATE products SET qtyinstock=3 WHERE productname='yellow' ;" after tSQL put "UPDATE products SET qtyinstock=34 WHERE productname='red' ;" after tSQL revexecuteSQL gConnectionID, tSQL The SQLStatement may contain one or more placeholders, which are sequential numbers prepended by a colon (:). The revExecuteSQL command substitutes the corresponding item in the variablesList for each of these placeholders. For example, if you have two variables called valueX and valueY, you can use a SQLStatement that includes placeholders as follows: revExecuteSQL myID, "insert into mytable values(:1,:2,:1)", "valueX" ,"valueY" The content of the variable valueX is substituted for the ":1" in the SQLQuery (in both places where ":1" appears), and the content of valueY is substituted for ":2". Placeholders can currently only represent data values in the query. The following line for example is incorrect and will cause a syntax error to be returned by the database because a placeholder is being used to represent a column name (:1). -- Invalid, will cause SQL syntax error revExecuteSQL myID,"insert into mytable(:1, name, date) values (:2, :3, :2)", "tColumnName", "tValueX", "tValueY" If you specify an arrayName rather than a list of ordinary variables, the revExecuteSQL command substitutes the corresponding element of the array for each of the placeholders in the query: revExecuteSQL myID,"insert into mytable values(:1,:2,:1)","myArray" The content of the element myArray1 is substituted for the ":1" in the SQLQuery (in both places where ":1" appears), and the content of myArray2 is substituted for ":2". Note: Some database systems, such as Oracle, SQLite and PostgreSQL, are capable of handling a multiple-line SQL statement. To pass binary data in a variable in the variablesList, prepend "*b" to the variable name. The revExecuteSQL command strips the binary marker "*b" and passes it to the database as binary data, rather than text data. To pass binary data in an array element, prepend "*b" to the element's key. The revExecuteSQL command is part of the Database library. To ensure that the command works in a standalone application, you must include this custom library when you create your standalone. In the Inclusions pane of the Standalone Application Settings window, make sure both the "Database" library checkbox and those of the database drivers you are using are checked. Parameters: *databaseID: The number returned by the revOpenDatabase function when the databasewas opened. *SQLStatement (string): A string in Structured Query Language. (Do not include a semicolon at the end of the SQL Statement.) Note: Some database systems, such as Oracle, are capable of handling a multiple-line SQL statement. *variablesList: The variablesList consists of one or more variable names (or expressions that evaluate to variable names), separated by commas. Each variable name may also be the name of an array element, for example "tInputDataid". *arrayName (array): The name of a single array variable whose keys are sequential numbers. Note: The variable names or arrayName must be enclosed in quotes; otherwise, the variable's value rather than its name is passed to the revExecuteSQL command. * The result:The revExecuteSQL command places a return value into the result, to indicate the outcome of the query. For successful queries, the revExecuteSQL command returns the number of rows affected for INSERT,UPDATE and DELETE statements. For all other statements, 0 is returned. *For unsuccessful queries, an error string is returned, describing the problem. See also: revDatabaseTableNames (function),revOpenDatabase (function),result (function),revQueryDatabase (function),revDataFromQuery (function),revdb_execute (function), Category: database